package DAOMysql;

import java.sql.*;
import java.util.*;

import java.util.logging.Level;

import javax.servlet.http.HttpSession;
//import com.sun.istack.internal.logging.Logger;

import model.*;
import DAO.*;

public class FacilitiesDAOImpl implements FacilitiesDAO {

	private static final String dbUrl = "jdbc:mysql://localhost:3306/fbsdatabase";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	
	public FacilitiesDAOImpl() {}
	public ArrayList<FacilitiesDTO>FindAll() throws NotFoundException {
		ArrayList<FacilitiesDTO> resultlist = new ArrayList<FacilitiesDTO>();
		try {		   
			Class.forName("com.mysql.jdbc.Driver");
		   } catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		 }
	    String sql = "SELECT * FROM Facilities ORDER BY FacilityID ASC ";
	    Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				FacilitiesDTO f = new FacilitiesDTO();
				f.setFacilityID(rs.getInt("FacilityId"));
				f.setFacilityName(rs.getString("FacilityName"));
				f.setType(rs.getString("Type"));
				f.setDescription(rs.getString("Description"));
				f.setCapacity(rs.getInt("Capacity"));
				resultlist.add(f);
			}
			conn.commit();
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			throw new NotFoundException(error);
		} finally {
			try {
				//conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return resultlist;
	}
	public FacilitiesDTO findFacility(Integer ID) throws  NotFoundException {
		String sql = "SELECT * FROM Facilities WHERE FacilityID='" + ID +"';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		FacilitiesDTO  facility = new FacilitiesDTO ();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				facility.setFacilityID(rs.getInt("FacilityId"));
				facility.setFacilityName(rs.getString("FacilityName"));
				facility.setType(rs.getString("Type"));
				facility.setDescription(rs.getString("Description"));
				facility.setCapacity(rs.getInt("Capacity"));
			}
			conn.commit();
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
		//	throw new DAOException(error);
		} finally {
			//try {
				//conn.rollback();
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			//} catch (Exception e) {
			//	e.printStackTrace();
			//}
		}
		return facility;
	}

	public ArrayList<FacilitiesDTO> FindByType(String type) throws NotFoundException{
		ArrayList<FacilitiesDTO> Type = new ArrayList<FacilitiesDTO>();
		try{
			Class.forName("com.mysql.jdbc.Driver");
		   } catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		 }
		String sql = "SELECT * FROM Facilities WHERE Type='" + type.trim() + "'ORDER BY FacilityID ASC";
	    Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				FacilitiesDTO f = new FacilitiesDTO();
				f.setFacilityID(rs.getInt("FacilityId"));
				f.setFacilityName(rs.getString("FacilityName"));
				f.setType(rs.getString("Type"));
				f.setDescription(rs.getString("Description"));
				f.setCapacity(rs.getInt("Capacity"));
				Type.add(f);
			}
			conn.commit();
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			throw new NotFoundException(error);
		} finally {
			try {
				//conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return Type;
	}
    public void deleteFacilities(FacilitiesDTO facilities) throws NotFoundException {
    	String deleteSql = "DELETE FROM facilities WHERE FacilityID='" + facilities.getFacilityID() + "';";
    		try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    		Connection conn = null;
    		try {
    			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    			conn.setAutoCommit(false);
    			Statement st = conn.createStatement();
    			int rows = st.executeUpdate(deleteSql);
    			conn.commit();
    			st.close();
    		} catch (Exception e) {
    			String error = "Error deleting facilitiess. Nested Exception is: " + e;
    			throw new NotFoundException(error);
    		} finally {
    			try {
    				//conn.rollback();
    				conn.close();
    			} catch (Exception e) {    				
    			}
    		}
    	}
       @SuppressWarnings("finally")
	public boolean IsValid(String fID) throws NotFoundException
       {
        //boolean flag1=true;
        //boolean flag2=false;
        boolean valid = false;
        String duplicate = "SELECT facilityID from facilities where facilityID='"+fID+"'";
        Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			ResultSet rs=st.executeQuery(duplicate);
			if ( rs.next() )
				valid = false;
			else
				valid = true;
				
			st.close();
       } catch (Exception e) {
			String error = "Error deleting facilitiess. Nested Exception is: " + e;
			throw new NotFoundException(error);
		} finally {
			try{
				//conn.rollback();
				conn.close();
			//	return flag1;
			}catch(Exception e){
				System.out.println(e);
			}
			return valid;
		}    				
	}
    	public void insertFacilities(FacilitiesDTO facilities) throws NotFoundException {
    		
    		String insertSql = "INSERT INTO facilities VALUES('"+facilities.getFacilityID()+"', '"+facilities.getFacilityName()+"', '"+facilities.getType()+"', '"+facilities.getDescription()+"', '"+facilities.getCapacity()+"');";
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e1) {
    			e1.printStackTrace();
    		}    		
    		Connection conn = null;
    		try {
    			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    			conn.setAutoCommit(false);
    			Statement st = conn.createStatement();
    			int res = st.executeUpdate(insertSql);
    			conn.commit();
    			st.close();   			
    		} catch (Exception e) {
    			String error = "Error updating facilities. Nested Exception is: " + e;
    			throw new NotFoundException(error);
    			
    		} finally {
    			try {
    				//conn.rollback();
    				conn.close();
    			} catch (Exception e) { 
    			}
    		}  		
    	}
    	public void updateFacilities(FacilitiesDTO facilities) throws NotFoundException {
    		String updateSql = "UPDATE facilities SET FacilityID='"+facilities.getFacilityID()+"', FacilityName= '"+facilities.getFacilityName()+"', Type= '"+facilities.getType()+"', Description= '"+facilities.getDescription()+"', Capacity= '"+facilities.getCapacity()+"' WHERE FacilityID='"+facilities.getFacilityID()+"';";
    		System.out.println(updateSql);
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e1) {
    			e1.printStackTrace();
    		}
    		Connection conn = null;
    		try {
    			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    			conn.setAutoCommit(true);
    			Statement st = conn.createStatement();
    			int res = st.executeUpdate(updateSql);
    			System.out.println(res);
    			st.close();
//    			conn.commit();
    		} catch (Exception e) {
    			String error = "Error updating facilities. Nested Exception is: " + e;
    			throw new NotFoundException(error);
    		} finally {
    			try {
    				//conn.rollback();
    				conn.close();
    			} catch (Exception e) {   				
    			}
    		}
    	}
}
	

